﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Bitirme.DAL.Entities;
using System.Data.SqlClient;
using Bitirme.DAL.Utils;
using System.Data;

namespace Bitirme.DAL.Data_Access
{
    public class AssignmentCollection
    {
        public static int InsertAssignment(Assignment assignment)
        {

            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parEmployeeID = null;
            SqlParameter parWorkPackageID = null;
            SqlParameter parStartDate = null;
            SqlParameter parEndDate = null;
            SqlParameter parHourPerDay = null;
            SqlParameter parReturnValue = null;

            int insertedAssignmentID = -1;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("InsertAssignment", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??

                parEmployeeID = new SqlParameter();
                parEmployeeID.ParameterName = "@employeeID";
                parEmployeeID.Value = assignment.Employee.ID;
                parEmployeeID.SqlDbType = SqlDbType.Int;

                parWorkPackageID = new SqlParameter();
                parWorkPackageID.ParameterName = "@workpackageID";
                parWorkPackageID.Value = assignment.WorkPackage.ID;
                parWorkPackageID.SqlDbType = SqlDbType.Int;

                parStartDate = new SqlParameter();
                parStartDate.ParameterName = "@startdate";
                parStartDate.Value = assignment.StartDate;
                parStartDate.SqlDbType = SqlDbType.DateTime;

                parEndDate = new SqlParameter();
                parEndDate.ParameterName = "@enddate";
                parEndDate.Value = assignment.EndDate;
                parEndDate.SqlDbType = SqlDbType.DateTime;

                parHourPerDay = new SqlParameter();
                parHourPerDay.ParameterName = "@hourperday";
                parHourPerDay.Value = assignment.HourPerDay;
                parHourPerDay.SqlDbType = SqlDbType.Int;

                parReturnValue = new SqlParameter();
                parReturnValue.Direction = ParameterDirection.ReturnValue;
                parReturnValue.ParameterName = "@ID";

                cmd.Parameters.Add(parWorkPackageID);
                cmd.Parameters.Add(parStartDate);
                cmd.Parameters.Add(parEndDate);
                cmd.Parameters.Add(parHourPerDay);
                cmd.Parameters.Add(parReturnValue);
                cmd.Parameters.Add(parEmployeeID);

                myConnection.Open();
                cmd.ExecuteScalar();
                Int32.TryParse(parReturnValue.Value.ToString(), out insertedAssignmentID);
            }
            catch (SqlException e)
            {

                insertedAssignmentID = -1;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }
            return insertedAssignmentID;
        }
        public static Boolean UpdateAssignmentByID(Assignment assignment)
        {

            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parID = null;
            SqlParameter parEmployeeID = null;
            SqlParameter parWorkPackageID = null;
            SqlParameter parStartDate = null;
            SqlParameter parEndDate = null;
            SqlParameter parHourPerDay = null;


            bool isinsertedAssignment = false;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("UpdateAssignmentByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??


                parID = new SqlParameter();
                parID.ParameterName = "@ID";
                parID.Value = assignment.ID;
                parID.SqlDbType = SqlDbType.Int;

                parEmployeeID = new SqlParameter();
                parEmployeeID.ParameterName = "@employeeID";
                parEmployeeID.Value = assignment.Employee.ID;
                parEmployeeID.SqlDbType = SqlDbType.Int;

                parWorkPackageID = new SqlParameter();
                parWorkPackageID.ParameterName = "@workpackageID";
                parWorkPackageID.Value = assignment.WorkPackage.ID;
                parWorkPackageID.SqlDbType = SqlDbType.Int;

                parStartDate = new SqlParameter();
                parStartDate.ParameterName = "@startdate";
                parStartDate.Value = assignment.StartDate;
                parStartDate.SqlDbType = SqlDbType.DateTime;

                parEndDate = new SqlParameter();
                parEndDate.ParameterName = "@enddate";
                parEndDate.Value = assignment.EndDate;
                parEndDate.SqlDbType = SqlDbType.DateTime;

                parHourPerDay = new SqlParameter();
                parHourPerDay.ParameterName = "@hourperday";
                parHourPerDay.Value = assignment.HourPerDay;
                parHourPerDay.SqlDbType = SqlDbType.Int;

                cmd.Parameters.Add(parID);
                cmd.Parameters.Add(parEmployeeID);
                cmd.Parameters.Add(parWorkPackageID);
                cmd.Parameters.Add(parStartDate);
                cmd.Parameters.Add(parEndDate);
                cmd.Parameters.Add(parHourPerDay);

                isinsertedAssignment = true;
                myConnection.Open();
                cmd.ExecuteScalar();
            }
            catch (SqlException e)
            {
                isinsertedAssignment = false;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }
            return isinsertedAssignment;
        }
        public static Boolean DeleteAssignmentByID(int assignmentID)
        {
            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parAssignmentID = null;
            bool isDeleteSuccessful = false;

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("DeleteAssignmentByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parAssignmentID = new SqlParameter();
                parAssignmentID.ParameterName = "@ID";
                parAssignmentID.Value = assignmentID;
                parAssignmentID.SqlDbType = SqlDbType.Int;


                cmd.Parameters.Add(parAssignmentID);


                myConnection.Open();

                cmd.ExecuteScalar();
                isDeleteSuccessful = true;
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
                isDeleteSuccessful = false;
            }

            finally
            {

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return isDeleteSuccessful;
        }
        public static List<Assignment> GetAllAssignments()
        {
            SqlConnection myConnection = null;
            SqlDataReader myReader = null;
            SqlCommand cmd = null;
            List<Assignment> assignments = new List<Assignment>();

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("GetAllAssignments", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                myConnection.Open();
                myReader = cmd.ExecuteReader();
                while (myReader.Read())
                {
                    Assignment tempAssignment = new Assignment();
                    Employee tempEmployee = new Employee();
                    WorkPackage tempWorkPackage = new WorkPackage();

                    int ID;
                    Int32.TryParse(SQLUtils.NVL(myReader["ID"], 0).ToString(), out ID);
                    tempAssignment.ID = ID;
                    
                    int employeeID;
                    Int32.TryParse(SQLUtils.NVL(myReader["EMPLOYEE_ID"], 0).ToString(), out employeeID);
                    tempEmployee.ID = employeeID;

                    int workpackageID;
                    Int32.TryParse(SQLUtils.NVL(myReader["WORKPACKAGE_ID"], 0).ToString(), out workpackageID);
                    tempWorkPackage.ID = workpackageID;

                    int hourPerDay;
                    Int32.TryParse(SQLUtils.NVL(myReader["HOUR_PER_DAY"], 0).ToString(), out hourPerDay);
                    tempAssignment.HourPerDay = hourPerDay;

                    
                    
                    
                    //tempAssignment.Name = SQLUtils.NVL(myReader["Name"], String.Empty).ToString();
                    //tempAssignment.SurName = SQLUtils.NVL(myReader["Surname"], String.Empty).ToString();

                    assignments.Add(tempAssignment);
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            finally
            {
                if (myReader != null)
                {
                    myReader.Close();
                    myReader.Dispose();
                }

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return assignments;
        }
        public static List<Assignment> GetAssignmentsByProjectID(int projectID)
        {
            SqlConnection myConnection = null;
            SqlDataReader myReader = null;
            SqlCommand cmd = null;
            SqlParameter parProjectID = null;
            List<Assignment> assignments = new List<Assignment>();

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("GetAssignmentsByProjectID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parProjectID = new SqlParameter();
                parProjectID.ParameterName = "@projectID";
                parProjectID.Value = projectID;
                parProjectID.SqlDbType = SqlDbType.Int;


                cmd.Parameters.Add(parProjectID);

                myConnection.Open();
                myReader = cmd.ExecuteReader();
                while (myReader.Read())
                {
                    Assignment tempAssignment = new Assignment();
                    Employee tempEmployee = new Employee();
                    WorkPackage tempWorkPackage = new WorkPackage();

                    int ID;
                    Int32.TryParse(SQLUtils.NVL(myReader["ID"], 0).ToString(), out ID);
                    tempAssignment.ID = ID;
                    int employeeID;
                    Int32.TryParse(SQLUtils.NVL(myReader["EMPLOYEE_ID"], 0).ToString(), out employeeID);
                    tempEmployee.ID = employeeID;
                    tempEmployee.Name = SQLUtils.NVL(myReader["employeeNAME"], String.Empty).ToString();
                    tempEmployee.SurName = SQLUtils.NVL(myReader["employeeSURNAME"], String.Empty).ToString();
                    tempEmployee.Email = SQLUtils.NVL(myReader["employeeEMAIL"], String.Empty).ToString();
                    int hoursperday;
                    Int32.TryParse(SQLUtils.NVL(myReader["employeeHOURS_PER_DAY"], 0).ToString(), out hoursperday);
                    tempEmployee.HourPerDay = hoursperday;
                    int salaryperday;
                    Int32.TryParse(SQLUtils.NVL(myReader["employeeSALARY_PER_DAY"], 0).ToString(), out salaryperday);
                    tempEmployee.SalaryPerDay = salaryperday;

                    int workpackageID;
                    Int32.TryParse(SQLUtils.NVL(myReader["WORK_PACKAGE_ID"], 0).ToString(), out workpackageID);
                    tempWorkPackage.ID = workpackageID;
                    tempWorkPackage.Name = SQLUtils.NVL(myReader["workPackageNAME"], String.Empty).ToString();

                    int hourperday;
                    Int32.TryParse(SQLUtils.NVL(myReader["HOUR_PER_DAY"], 0).ToString(), out hourperday);
                    tempAssignment.HourPerDay = hourperday;

                    tempAssignment.EndDate = Convert.ToDateTime(myReader["END_DATE"].ToString());
                    tempAssignment.StartDate = Convert.ToDateTime(myReader["START_DATE"].ToString());


                    tempAssignment.WorkPackage = tempWorkPackage;
                    tempAssignment.Employee = tempEmployee;

                    assignments.Add(tempAssignment);
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            finally
            {
                if (myReader != null)
                {
                    myReader.Close();
                    myReader.Dispose();
                }

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return assignments;
        }
        public static List<Assignment> GetAssignmentsByEmployeeID(int employeeID)
        {
            SqlConnection myConnection = null;
            SqlDataReader myReader = null;
            SqlCommand cmd = null;
            SqlParameter parProjectID = null;
            List<Assignment> assignments = new List<Assignment>();

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("GetAssignmentsByEmployeeID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parProjectID = new SqlParameter();
                parProjectID.ParameterName = "@employeeID";
                parProjectID.Value = employeeID;
                parProjectID.SqlDbType = SqlDbType.Int;


                cmd.Parameters.Add(parProjectID);

                myConnection.Open();
                myReader = cmd.ExecuteReader();
                while (myReader.Read())
                {
                    Assignment tempAssignment = new Assignment();
                    Employee tempEmployee = new Employee();
                    WorkPackage tempWorkPackage = new WorkPackage();

                    int ID;
                    Int32.TryParse(SQLUtils.NVL(myReader["ID"], 0).ToString(), out ID);
                    tempAssignment.ID = ID;
                    //int employeeID;
                    //Int32.TryParse(SQLUtils.NVL(myReader["EMPLOYEE_ID"], 0).ToString(), out employeeID);
                    tempEmployee.ID = employeeID;

                    int workpackageID;
                    Int32.TryParse(SQLUtils.NVL(myReader["WORK_PACKAGE_ID"], 0).ToString(), out workpackageID);
                    tempWorkPackage.ID = workpackageID;

                    tempWorkPackage.Name = SQLUtils.NVL(myReader["WORK_PACKAGE_NAME"], String.Empty).ToString();

                    int hourperday;
                    Int32.TryParse(SQLUtils.NVL(myReader["HOUR_PER_DAY"], 0).ToString(), out hourperday);
                    tempAssignment.HourPerDay = hourperday;

                    tempAssignment.EndDate = Convert.ToDateTime(myReader["END_DATE"].ToString());
                    tempAssignment.StartDate = Convert.ToDateTime(myReader["START_DATE"].ToString());


                    tempAssignment.WorkPackage = tempWorkPackage;
                    tempAssignment.Employee = tempEmployee;

                    assignments.Add(tempAssignment);
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            finally
            {
                if (myReader != null)
                {
                    myReader.Close();
                    myReader.Dispose();
                }

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return assignments;
        }
        public static List<Assignment> GetAssignmentsByWorkPackageID(int packageID)
        {
            SqlConnection myConnection = null;
            SqlDataReader myReader = null;
            SqlCommand cmd = null;
            SqlParameter parProjectID = null;
            List<Assignment> assignments = new List<Assignment>();

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("GetAssignmentsByWorkPackageID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parProjectID = new SqlParameter();
                parProjectID.ParameterName = "@packageID";
                parProjectID.Value = packageID;
                parProjectID.SqlDbType = SqlDbType.Int;


                cmd.Parameters.Add(parProjectID);

                myConnection.Open();
                myReader = cmd.ExecuteReader();
                while (myReader.Read())
                {
                    Assignment tempAssignment = new Assignment();
                    Employee tempEmployee = new Employee();
                    WorkPackage tempWorkPackage = new WorkPackage();

                    int ID;
                    Int32.TryParse(SQLUtils.NVL(myReader["ID"], 0).ToString(), out ID);
                    tempAssignment.ID = ID;
                    int employeeID;
                    Int32.TryParse(SQLUtils.NVL(myReader["EMPLOYEE_ID"], 0).ToString(), out employeeID);
                    tempEmployee.ID = employeeID;

                    tempWorkPackage.ID = packageID;

                    int hourperday;
                    Int32.TryParse(SQLUtils.NVL(myReader["HOUR_PER_DAY"], 0).ToString(), out hourperday);
                    tempAssignment.HourPerDay = hourperday;

                    tempAssignment.EndDate = Convert.ToDateTime(myReader["END_DATE"].ToString());
                    tempAssignment.StartDate = Convert.ToDateTime(myReader["START_DATE"].ToString());


                    tempAssignment.WorkPackage = tempWorkPackage;
                    tempAssignment.Employee = tempEmployee;

                    assignments.Add(tempAssignment);
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            finally
            {
                if (myReader != null)
                {
                    myReader.Close();
                    myReader.Dispose();
                }

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return assignments;
        }
        public static int InsertAssignmentToProject(int assignmentID, int projectID) {
        
            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parAssignmentID = null;
            SqlParameter parProjectID = null;
            SqlParameter parReturnValue = null;

            int insertedAssignmentToProjectID = -1;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("InsertAssignmentToProject", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??

                parAssignmentID = new SqlParameter();
                parAssignmentID.ParameterName = "@assignmentID";
                parAssignmentID.Value = assignmentID;
                parAssignmentID.SqlDbType = SqlDbType.Int;

                parProjectID = new SqlParameter();
                parProjectID.ParameterName = "@projectID";
                parProjectID.Value = projectID;
                parProjectID.SqlDbType = SqlDbType.Int;

                parReturnValue = new SqlParameter();
                parReturnValue.Direction = ParameterDirection.ReturnValue;
                parReturnValue.ParameterName = "@ID";

                cmd.Parameters.Add(parProjectID);
                cmd.Parameters.Add(parAssignmentID);
                cmd.Parameters.Add(parReturnValue);

                myConnection.Open();
                cmd.ExecuteScalar();
                Int32.TryParse(parReturnValue.Value.ToString(), out insertedAssignmentToProjectID);
            }
            catch (SqlException e)
            {

                insertedAssignmentToProjectID = -1;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }
            return insertedAssignmentToProjectID;
        
        
        }
        public static bool DeleteAssignmentsByWorkPackageID(int workpackageID) {

            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parWorkPackageID = null;
            bool isDeleteSuccessful = false;

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("DeleteAssignmentsByWorkPackageID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parWorkPackageID = new SqlParameter();
                parWorkPackageID.ParameterName = "@ID";
                parWorkPackageID.Value = workpackageID;
                parWorkPackageID.SqlDbType = SqlDbType.Int;


                cmd.Parameters.Add(parWorkPackageID);


                myConnection.Open();

                cmd.ExecuteScalar();
                isDeleteSuccessful = true;
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
                isDeleteSuccessful = false;
            }

            finally
            {

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return isDeleteSuccessful;
        
        
        }
        
    }
}